﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SQLite;
using System.IO;

using System.Web.Services;
using System.Text;
using System.Threading;
using System.Security.Cryptography;
using System.Web.Caching;

namespace WebProxy
{
    public class SQLiteDatabase
    {
        public string dbConnection;
        public string databasePath;
        public string databaseFileName;
        //System.Web.HttpContext.Current.Server.MapPath
        //public static string databasePath = System.Web.HttpContext.Current.Server.MapPath("~/") + databaseFileName;
        public SQLiteDatabase(){

            databaseFileName = "database.db";
            databasePath = System.Web.HttpContext.Current.Server.MapPath("~/") + databaseFileName;
            dbConnection = "Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/") + "database.db";

            if (!File.Exists(databasePath))
            {
                SQLiteConnection.CreateFile(databasePath);
                //utworz tabelki
                ExecuteNonQuery("CREATE TABLE IF NOT EXISTS [Notification]([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[Title] NVARCHAR(255) NULL,[Message] NVARCHAR(2048) NULL,[Uri] NVARCHAR(255) NULL,[avaFrom] INTEGER NOT NULL,[avaTo] INTEGER NOT NULL);");
                ExecuteNonQuery("CREATE TABLE IF NOT EXISTS [RefreshTime]([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[avaFrom] INTEGER NOT NULL,[avaTo] INTEGER NOT NULL,[minutes] INTEGER NOT NULL);");
            }
        }
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + dbConnection);
            }
            return dt;
        }
        public int ExecuteNonQuery(string sql)
        {
            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(cnn);
            mycommand.CommandText = sql;
            int rowsUpdated = mycommand.ExecuteNonQuery();
            cnn.Close();
            return rowsUpdated;
        }
        public bool DeleteOldItems()
        {
            bool returnCode = false;
            string sql;
            try
            {
                sql = String.Format("delete from Notification where {0} > avaTo",DateTime.Now.Ticks);
                this.ExecuteNonQuery(sql);
                sql = String.Format("delete from RefreshTime where {0} > avaTo", DateTime.Now.Ticks);
                this.ExecuteNonQuery(sql);
                returnCode = true;
            }
            catch(Exception e)
            {
                 throw new Exception(e.Message + dbConnection);
            }
            return returnCode;
        }
        public bool InsertNotification(NetcampNotification notification)
        {
            string sql;
            //DateTime asd = new DateTime(1);
            
            bool returnCode = false;
            try
            {
                sql = String.Format("insert into Notification (Title, Message, Uri, avaFrom, avaTo) VALUES ('{0}', '{1}', '{2}', {3}, {4})", notification.title, notification.message, notification.uriSite, notification.availableFrom.Ticks, notification.availableTo.Ticks);
                this.ExecuteNonQuery(sql);
                returnCode = true;
            }
            catch(Exception e)
            {
                throw new Exception(e.Message + dbConnection);
            }
            return returnCode;
        }
        public bool InsertRefresh(long avaFrom, long avaTo, int minutes)
        {
            string sql;
            bool returnCode = false;
            try
            {
                sql = String.Format("insert into RefreshTime (avaFrom, avaTo, minutes) VALUES ({0}, {1}, {2})",avaFrom,avaTo,minutes);
                this.ExecuteNonQuery(sql);
                returnCode = true;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + dbConnection);
            }
            return returnCode;
        }
    }
}